package org.compiere.model;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import javax.swing.JFileChooser;
import javax.swing.JList;
import javax.swing.filechooser.FileNameExtensionFilter;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;


public class CalloutBPM_FormConsolidUpload extends CalloutEngine {

	BigDecimal zero = Env.ZERO;
	MBPartner bp = null;
	
	
	private static CLogger s_log = CLogger.getCLogger(CalloutBPM_FormConsolidUpload.class);
	XSSFWorkbook wb;
//	List<TablesWithC_charge_ID> tablesWithC_charge_IDs;
	
	public String FileUpload(Properties ctx, int WindowNo, GridTab mTab,
			GridField mField, Object valuee) {
		String result = "";
		zero = zero.setScale(0, BigDecimal.ROUND_HALF_UP);
				
		//--------------------------
		System.out.println("Start: " + new Timestamp(System.currentTimeMillis()));
		
		try {
			
			File file = null;
			
				// try{
				 final JFileChooser fc = new JFileChooser();
				 JList list2 = new JList();
		//		 fc.remove(0);
				 fc.addChoosableFileFilter(new FileNameExtensionFilter("xls", "xls"));
				  fc.setFileSelectionMode(JFileChooser.FILES_ONLY);
				// FileOutputStream fos = null;
				// OutputStreamWriter out = null;
				//
				 String FileName = "";
				 if (JFileChooser.APPROVE_OPTION == fc.showOpenDialog(list2)) 
					 FileName = fc.getSelectedFile().getAbsolutePath();
				 
				 if(FileName.equals(""))
					 return "";
				 
				file = new File(FileName);
				wb = new XSSFWorkbook(new FileInputStream(file));
				MBPMFormConsolidation consolidation = new MBPMFormConsolidation(Env.getCtx(), mTab.getRecord_ID(), null);
//				MBPMForm form  = new MBPMForm(Env.getCtx(), consolidation.getBPM_Form_ID(), null);
				
//				System.out.println(consolidation.getBPM_Subs().getBPM_Subs_ID());
//				System.out.println(consolidation.getBPM_Project_ID());
//				System.out.println(consolidation.getBPM_VersionBudget_ID());
//				System.out.println(consolidation.getBPM_Form_ID());
//				System.out.println(form.getName());
				
				// form 2, 2 sheet -------------------------------	БДДС
				if(consolidation.getBPM_Form_ID() == 1000033){

					
					int is = checkLines(consolidation);
					if(is == 0){
						List<Integer> lines = getLines(1000005);
						for(int i=0; i< lines.size(); i++){
							MBPMFormCell cell = MBPMFormCell.getCell(Env.getCtx(), 1000010, lines.get(i), null);
							MBPMFormValues value = new MBPMFormValues(Env.getCtx(), 0, null);
							value.setBPM_Form_ID(consolidation.getBPM_Form_ID());
							value.setBPM_FormLine_ID(lines.get(i));
							value.setBPM_FormCell_ID(cell.getBPM_FormCell_ID());
							value.setBPM_Project_ID(consolidation.getBPM_Project_ID());
							value.setAD_Org_ID(consolidation.getAD_Org_ID());
							value.setBPM_VersionBudget_ID(consolidation.getBPM_VersionBudget_ID());
							value.setBPM_FormColumn_ID(1000010);
							value.save();
							
						}
						
					}
					
					XSSFSheet sheet = wb.getSheetAt(0);
					List<Double> list = read5(sheet);
					List<MBPMFormValues> values = getValues(consolidation);
					for(int i=0; i< values.size();i++){
						MBPMFormValues v = values.get(i);
						Double d = .0;
						if(list.size() >= i)
							d = list.get(i);
						v.setCellValue(new BigDecimal(d).toString());
						v.save();
					}
				}
				// form 6 BO , 1 shett	-------------------------------
				if(consolidation.getBPM_Form_ID() == 1000034){
				
					int is = checkLines(consolidation);
					if(is == 0){
						List<Integer> lines = getLines(1000010);
						for(int i=0; i< lines.size(); i++){
							MBPMFormCell cell = MBPMFormCell.getCell(Env.getCtx(), 1000040, lines.get(i), null);
							MBPMFormValues value = new MBPMFormValues(Env.getCtx(), 0, null);
							value.setBPM_Form_ID(consolidation.getBPM_Form_ID());
							value.setBPM_FormLine_ID(lines.get(i));
							value.setBPM_FormCell_ID(cell.getBPM_FormCell_ID());
							value.setBPM_Project_ID(consolidation.getBPM_Project_ID());
							value.setAD_Org_ID(consolidation.getAD_Org_ID());
							value.setBPM_VersionBudget_ID(consolidation.getBPM_VersionBudget_ID());
							value.setBPM_FormColumn_ID(1000040);
							value.save();
							
						}
						
					}
					XSSFSheet sheet = wb.getSheetAt(0);	
					List<Double> list = read6(sheet);
					List<MBPMFormValues> values = getValues(consolidation);
					for(int i=0; i< values.size();i++){
						MBPMFormValues v = values.get(i);
						Double d = .0;
						if(list.size() >= i)
							d = list.get(i);
						v.setCellValue(new BigDecimal(d).toString());
						v.save();
					}
				}
				// form 5 BOp , 3 sheet-------------------------------	БДР
				
				if(consolidation.getBPM_Form_ID() == 1000035){
					int is = checkLines(consolidation);
					if(is == 0){
						List<Integer> lines = getLines(1000008);
						for(int i=0; i< lines.size(); i++){
							MBPMFormCell cell = MBPMFormCell.getCell(Env.getCtx(), 1000028, lines.get(i), null);
							MBPMFormValues value = new MBPMFormValues(Env.getCtx(), 0, null);
							value.setBPM_Form_ID(consolidation.getBPM_Form_ID());
							value.setBPM_FormLine_ID(lines.get(i));
							value.setBPM_FormCell_ID(cell.getBPM_FormCell_ID());
							value.setBPM_Project_ID(consolidation.getBPM_Project_ID());
							value.setAD_Org_ID(consolidation.getAD_Org_ID());
							value.setBPM_VersionBudget_ID(consolidation.getBPM_VersionBudget_ID());
							value.setBPM_FormColumn_ID(1000028);
							value.save();
							
						}
						
					}
					XSSFSheet sheet = wb.getSheetAt(0);
					List<Double> list = read1(sheet);
					List<MBPMFormValues> values = getValues(consolidation);
					for(int i=0; i< values.size();i++){
						MBPMFormValues v = values.get(i);
						Double d = .0;
						if(list.size() >= i)
							d = list.get(i);
						v.setCellValue(new BigDecimal(d).toString());
						v.save();
					}
		}
				

				
				
			}catch(Exception e){
				System.out.println(e.getMessage());
			}

		return result;
		
	}			
				

	private List<Double> read5(XSSFSheet sheet) {
		if(sheet == null) return null;
		
		List<Double> result = new ArrayList<Double>();
		String s = "";
		for (Row row : sheet) {
			
			s = "";
			if (row.getRowNum() < 3)
				continue;

			if (row.getRowNum() > 75)
				continue;

			
			for (Cell cell : row) {
				if (cell.getColumnIndex() == 2) {
					
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
						Double value = cell.getNumericCellValue();
//						value.longValue()
						s = Long.toString(value.longValue());// Double.toString(value);
					}else 

					if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
						XSSFFormulaEvaluator ev = new XSSFFormulaEvaluator(wb);
						CellValue cv = ev.evaluate(cell);
						if (cv.getCellType() == Cell.CELL_TYPE_NUMERIC){
							Double value = cv.getNumberValue();
							s = Long.toString(value.longValue());// Double.toString(value);
						}						
					} 
					else s = "0";//continue;
					
					
					if (s.equals(""))
						continue;
					
						result.add(Double.valueOf(s));
				}

			}

		}
		return result;

	}
	
	
	private List<Double> read6(XSSFSheet sheet) {
		if(sheet == null) return null;
		
		List<Double> result = new ArrayList<Double>();
		String s = "";
		for (Row row : sheet) {
			
			s = "";
			if (row.getRowNum() < 3)
				continue;

			if (row.getRowNum() > 38)
				continue;

			
			for (Cell cell : row) {
				if (cell.getColumnIndex() == 2) {
					
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
						Double value = cell.getNumericCellValue();
//						value.longValue()
						s = Long.toString(value.longValue());// Double.toString(value);
					}else 

					if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
						XSSFFormulaEvaluator ev = new XSSFFormulaEvaluator(wb);
						CellValue cv = ev.evaluate(cell);
						if (cv.getCellType() == Cell.CELL_TYPE_NUMERIC){
							Double value = cv.getNumberValue();
							s = Long.toString(value.longValue());// Double.toString(value);
						}						
					} 
					else s = "0";//continue;
					
					
					if (s.equals(""))
						continue;
					
						result.add(Double.valueOf(s));
				}

			}

		}
		return result;

	}
	
	
	private List<Double> read1(XSSFSheet sheet) {
		if(sheet == null) return null;
		
		List<Double> result = new ArrayList<Double>();
		String s = "";
		for (Row row : sheet) {
			
			s = "";
			if (row.getRowNum() < 3)
				continue;

			if (row.getRowNum() > 37)
				continue;

			
			for (Cell cell : row) {
				if (cell.getColumnIndex() == 3) {
					
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
						Double value = cell.getNumericCellValue();
//						value.longValue()
						s = Long.toString(value.longValue());// Double.toString(value);
					}else 

					if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
						XSSFFormulaEvaluator ev = new XSSFFormulaEvaluator(wb);
						CellValue cv = ev.evaluate(cell);
						if (cv.getCellType() == Cell.CELL_TYPE_NUMERIC){
							Double value = cv.getNumberValue();
							s = Long.toString(value.longValue());// Double.toString(value);
						}						
					} 
					else s = "0";//continue;
					
					
					if (s.equals(""))
						continue;
					
						result.add(Double.valueOf(s));
				}

			}

		}
		return result;

	}

	
	
	private int checkLines(MBPMFormConsolidation consolidation) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = 0;
		
		String sql = "	select count(*) from adempiere.bpm_formvalues	\n"	+ 
				"	where bpm_subs_id = " + consolidation.getBPM_Subs().getBPM_Subs_ID()	+
				"	and bpm_project_id = " +	consolidation.getBPM_Project_ID()	+ 
				"	and bpm_versionbudget_id = "	+	consolidation.getBPM_VersionBudget_ID()	+	
				"	and bpm_form_id = "	+	consolidation.getBPM_Form_ID();

		try {
			pstmt = DB.prepareStatement(sql, null);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: ", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
		
	}	
	
	private List<Integer> getLines(int form_id) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Integer> result = new ArrayList<Integer>();
		
		String sql = 
				  
				  "			select bpm_formline_id	\n"
				+ "			from adempiere.bpm_formline 	\n"
				+ "			where bpm_form_id = "+form_id+"	 order by bpm_formline_id	\n"
				;


		try {
			pstmt = DB.prepareStatement(sql, null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				result.add(rs.getInt(1));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: ", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
		
	}

	private List<MBPMFormValues> getValues(MBPMFormConsolidation consolidation) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<MBPMFormValues> result = new ArrayList<MBPMFormValues>();
		
		String sql = "	select bpm_formvalues_id from adempiere.bpm_formvalues	\n"	+ 
				"	where bpm_subs_id = " + consolidation.getBPM_Subs().getBPM_Subs_ID()	+
				"	and bpm_project_id = " +	consolidation.getBPM_Project_ID()	+ 
				"	and bpm_versionbudget_id = "	+	consolidation.getBPM_VersionBudget_ID()	+	
				"	and bpm_form_id = "	+	consolidation.getBPM_Form_ID()	+
				"	order by bpm_formline_id	";

		try {
			pstmt = DB.prepareStatement(sql, null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				MBPMFormValues v = new MBPMFormValues(Env.getCtx(), rs.getInt(1), null);
				result.add(v);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: ", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
		
	}
	
	
}



